USE [BDCole]
GO
/****** Object:  StoredProcedure [dbo].[DeleteAlumno]    Script Date: 09/07/2015 12:16:48 a.m. ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE procedure [dbo].[DeleteAlumno]
@id int
as
begin
	if exists(select * from Alumno where id = @id)
		delete Alumno where id = @id 
end
GO
/****** Object:  StoredProcedure [dbo].[DeleteCurso]    Script Date: 09/07/2015 12:16:48 a.m. ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE procedure [dbo].[DeleteCurso]
@id int
as
begin
	if exists(select * from Cursos where id = @id)
		delete Cursos where id = @id 
end
GO
/****** Object:  StoredProcedure [dbo].[DeleteDocente]    Script Date: 09/07/2015 12:16:48 a.m. ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE procedure [dbo].[DeleteDocente]
@id int
as
begin
	if exists(select * from Docente where id = @id)
		delete Docente where id = @id 
end

GO
/****** Object:  StoredProcedure [dbo].[GetAllFromDocentes]    Script Date: 09/07/2015 12:16:48 a.m. ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE procedure [dbo].[GetAllFromDocentes]
as begin

SELECT        Docente.Nombres, Docente.Apellidos, Docente.Especialidad, Docente.Email, Docente.Telefono
				
FROM            Docente 
  end
GO
/****** Object:  StoredProcedure [dbo].[GetAllFromTipo]    Script Date: 09/07/2015 12:16:48 a.m. ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/****** Script para el comando SelectTopNRows de SSMS  ******/

create procedure [dbo].[GetAllFromTipo]
as 
begin
SELECT Id
      ,Descripcion
      ,Comentario
  FROM TipoUsuario
  end
GO
/****** Object:  StoredProcedure [dbo].[GetAllGrado]    Script Date: 09/07/2015 12:16:48 a.m. ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create procedure [dbo].[GetAllGrado]
as
begin
	select 
	   * 
	From Grado
	
end
GO
/****** Object:  StoredProcedure [dbo].[GetAlumnoByApellidosNombresDNI]    Script Date: 09/07/2015 12:16:48 a.m. ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE procedure [dbo].[GetAlumnoByApellidosNombresDNI]
@criterio varchar(250)
as
begin
	select 
	   * 
	From Alumno
	where  UPPER(apellidos) LIKE UPPER(@criterio) or UPPER(nombres) LIKE UPPER(@criterio) or   nroDni =@criterio 
end

GO
/****** Object:  StoredProcedure [dbo].[GetCursoById]    Script Date: 09/07/2015 12:16:48 a.m. ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE procedure [dbo].[GetCursoById]
@criterio varchar(250)
as
begin
	select 
	   * 
	From Cursos
	where  UPPER(Nombre) LIKE UPPER(@criterio) or UPPER(Profesor) LIKE UPPER(@criterio) or   id =@criterio 
end
GO
/****** Object:  StoredProcedure [dbo].[GetFromDocentesByCriterio]    Script Date: 09/07/2015 12:16:48 a.m. ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE procedure [dbo].[GetFromDocentesByCriterio]
@criterio varchar(80),
@TipoUsuarioId int =0
as begin
	if @TipoUsuarioId = 0
		begin
			select d.id,
				
				d.Nombres,
				d.Apellidos,
				
				d.Email,
			d.Telefono,
				d.Especialidad,
				d.IdTipoUsuario
				
			From Docente as d inner join TipoUsuario as tu on d.IdTipoUsuario = tu.id
			where (upper(d.Nombres) like upper(@criterio) or UPPER(d.Especialidad) like upper(@criterio)) 
		end
	else
		begin
			select d.id,
				
				d.Nombres ,
				d.Apellidos,
				
				d.Email,
			d.Telefono,
				d.Especialidad
			From Docente as d inner join TipoUsuario as tu on d.IdTipoUsuario = tu.id
			where (upper(d.Nombres) like upper(@criterio) or UPPER(d.Especialidad) like upper(@criterio)) 
				
		end 
end

GO
/****** Object:  StoredProcedure [dbo].[GetFromDocentesById]    Script Date: 09/07/2015 12:16:48 a.m. ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE procedure [dbo].[GetFromDocentesById]
@id int
as
begin
	select docente.id,
		Nombres,
		Apellidos,
		Especialidad,
		Email,
		Telefono		
	From Docente 
	where docente.id = @id
end

GO
/****** Object:  StoredProcedure [dbo].[RegistrarAlumno]    Script Date: 09/07/2015 12:16:48 a.m. ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE procedure [dbo].[RegistrarAlumno]
@id int out,
@apellidos varchar(250),
@nombres varchar(250),
@fecha datetime ,
@nroDni	varchar(8)



as
Begin
	
	if not exists (select * from Alumno where id = @id)
	begin
		insert into Alumno(apellidos,nombres,fechaNacimiento,nroDni)
			values(@apellidos,@nombres,@fecha,@nroDni)
			
			set @id = SCOPE_IDENTITY()
	end
	else
		update Alumno
		set 
			apellidos = @apellidos,
			nombres=@nombres,
			fechaNacimiento = @fecha,
			nroDni = @nroDni
		where id = @id
End
GO
/****** Object:  StoredProcedure [dbo].[RegistrarCurso]    Script Date: 09/07/2015 12:16:48 a.m. ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE procedure [dbo].[RegistrarCurso]
@id int out,
@Nombre varchar(50),
@Profesor varchar(50),
@Descripcion	varchar(50)



as
Begin
	
	if not exists (select * from Cursos where id = @id)
	begin
		insert into Cursos(id,Nombre,Profesor,Descripcion)
			values(@id,@Nombre,@Profesor,@Descripcion)
			
			set @id = SCOPE_IDENTITY()
	end
	else
		update Cursos
		set 
			Nombre = @Nombre,
			Profesor=@Profesor,
			Descripcion = @Descripcion
			
		where id = @id
End

GO
/****** Object:  StoredProcedure [dbo].[RegistrarDetalleMatricula]    Script Date: 09/07/2015 12:16:48 a.m. ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
Create PROCEDURE [dbo].[RegistrarDetalleMatricula]
@item int out,
@IdMatricula int,
@IdCurso int,
@cantidad int

AS
BEGIN
	if not exists(select * from DetalleMatricula where item = @item and IdMatricula = @IdMatricula)
		begin
			insert into DetalleMatricula(IdMatricula,IdCurso,Cantidad)
			values(@IdMatricula,@IdCurso,@cantidad)

			SET @item = SCOPE_IDENTITY()
		end
	else		
		update DetalleMatricula
			set IdCurso = @IdCurso,
				Cantidad = @cantidad
			where item = @item and IdMatricula = @IdMatricula
END


GO
/****** Object:  StoredProcedure [dbo].[RegistrarDocente]    Script Date: 09/07/2015 12:16:48 a.m. ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create procedure [dbo].[RegistrarDocente]
@Id int out,
@Nombres varchar(250),
@Apellidos varchar(250),
@Especialidad varchar(250),
@Email varchar(250),
@Telefono varchar(10),
@idTipoUsuario int



as
Begin
	
	if not exists (select * from Docente where id = @id)
	begin
		insert into Docente(Nombres,Apellidos,Especialidad,Email,Telefono,IdTipoUsuario)
			values(@Nombres,@Apellidos,@Especialidad,@Email,@Telefono,@idTipoUsuario)
			
			set @id = SCOPE_IDENTITY()
	end
	else
		update Docente
		set 
			apellidos = @apellidos,
			nombres=@nombres,
			Especialidad = @Especialidad,
			Email = @Email,
			Telefono = @Telefono,
			IdTipoUsuario =@idTipoUsuario
		where id = @id
End
GO
/****** Object:  StoredProcedure [dbo].[RegistrarMatricula]    Script Date: 09/07/2015 12:16:48 a.m. ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[RegistrarMatricula]
@id int out,
@IdAlumno int,
@fecha datetime
AS
BEGIN

	if not exists(select * from Matricula where @id = id)
		begin
			insert into matricula(IdAlumno,fecha)
			values(@IdAlumno,@fecha)

			SET @id = SCOPE_IDENTITY()
		end
	else
		update matricula
		set IdAlumno = @IdAlumno,
			fecha = @fecha
			
		where id = @id
END
GO
/****** Object:  StoredProcedure [dbo].[registrarSeccion]    Script Date: 09/07/2015 12:16:48 a.m. ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE procedure [dbo].[registrarSeccion]
@id int out,
@nombres varchar(250),
@id_grado int



as
Begin
	
	if not exists (select * from Seccion where id = @id)
	begin
		insert into Seccion(Nombre_Seccion,ID_Grado)
			values(@nombres,@id_grado)
			
			set @id = SCOPE_IDENTITY()
	end
	else
		update Seccion
		set 
			Nombre_Seccion=@nombres,
			ID_Grado = @id_grado
		where id = @id
End

GO
/****** Object:  Table [dbo].[Alumno]    Script Date: 09/07/2015 12:16:48 a.m. ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Alumno](
	[Id] [int] IDENTITY(15,1) NOT NULL,
	[apellidos] [varchar](250) NOT NULL,
	[nombres] [varchar](250) NOT NULL,
	[fechaNacimiento] [date] NOT NULL,
	[nroDni] [char](8) NOT NULL,
 CONSTRAINT [PK_Alumno] PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[Cursos]    Script Date: 09/07/2015 12:16:48 a.m. ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Cursos](
	[id] [numeric](5, 0) NOT NULL,
	[Nombre] [varchar](50) NOT NULL,
	[Profesor] [varchar](50) NOT NULL,
	[Descripcion] [varchar](100) NULL,
 CONSTRAINT [PK_Cursos] PRIMARY KEY CLUSTERED 
(
	[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[DetalleMatricula]    Script Date: 09/07/2015 12:16:48 a.m. ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[DetalleMatricula](
	[Item] [int] IDENTITY(1,1) NOT NULL,
	[IdMatricula] [int] NOT NULL,
	[IdCurso] [numeric](5, 0) NULL,
	[Cantidad] [int] NULL,
 CONSTRAINT [PK_DetalleMatricula] PRIMARY KEY CLUSTERED 
(
	[Item] ASC,
	[IdMatricula] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
/****** Object:  Table [dbo].[Docente]    Script Date: 09/07/2015 12:16:48 a.m. ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Docente](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[Nombres] [varchar](150) NOT NULL,
	[Apellidos] [varchar](150) NOT NULL,
	[Especialidad] [varchar](100) NULL,
	[Email] [varchar](50) NULL,
	[Telefono] [varchar](10) NULL,
	[IdTipoUsuario] [int] NOT NULL,
 CONSTRAINT [PK_Docente] PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[Grado]    Script Date: 09/07/2015 12:16:48 a.m. ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Grado](
	[Grado] [varchar](50) NOT NULL,
	[IdGrado] [numeric](5, 0) NOT NULL,
 CONSTRAINT [PK_Grado] PRIMARY KEY CLUSTERED 
(
	[IdGrado] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[Matricula]    Script Date: 09/07/2015 12:16:48 a.m. ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Matricula](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[IdAlumno] [int] NULL,
	[Fecha] [datetime] NULL,
 CONSTRAINT [PK_Matricula] PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
/****** Object:  Table [dbo].[Seccion]    Script Date: 09/07/2015 12:16:48 a.m. ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Seccion](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[Nombre_Seccion] [varchar](50) NOT NULL,
	[ID_Grado] [int] NOT NULL,
 CONSTRAINT [PK_Seccion] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[TipoUsuario]    Script Date: 09/07/2015 12:16:48 a.m. ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[TipoUsuario](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[Descripcion] [varchar](50) NOT NULL,
	[Comentario] [varchar](150) NULL,
 CONSTRAINT [PK_TipoUsuario] PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
SET IDENTITY_INSERT [dbo].[Alumno] ON 

INSERT [dbo].[Alumno] ([Id], [apellidos], [nombres], [fechaNacimiento], [nroDni]) VALUES (25, N'Tasilla Llanos', N'Jesus', CAST(N'2014-08-13' AS Date), N'12365478')
INSERT [dbo].[Alumno] ([Id], [apellidos], [nombres], [fechaNacimiento], [nroDni]) VALUES (26, N'Julcamoro Fernandez', N'Luisa', CAST(N'2015-07-30' AS Date), N'12321232')
SET IDENTITY_INSERT [dbo].[Alumno] OFF
INSERT [dbo].[Cursos] ([id], [Nombre], [Profesor], [Descripcion]) VALUES (CAST(1 AS Numeric(5, 0)), N'Matematica', N'Kevin', NULL)
INSERT [dbo].[Cursos] ([id], [Nombre], [Profesor], [Descripcion]) VALUES (CAST(2 AS Numeric(5, 0)), N'Comunicacion', N'Leandro', NULL)
INSERT [dbo].[Cursos] ([id], [Nombre], [Profesor], [Descripcion]) VALUES (CAST(3 AS Numeric(5, 0)), N'Historia', N'Javier', NULL)
INSERT [dbo].[Cursos] ([id], [Nombre], [Profesor], [Descripcion]) VALUES (CAST(4 AS Numeric(5, 0)), N'Ed.Fisica', N'Richard', NULL)
INSERT [dbo].[Cursos] ([id], [Nombre], [Profesor], [Descripcion]) VALUES (CAST(5 AS Numeric(5, 0)), N'Fisica', N'Gustavo', NULL)
SET IDENTITY_INSERT [dbo].[Docente] ON 

INSERT [dbo].[Docente] ([Id], [Nombres], [Apellidos], [Especialidad], [Email], [Telefono], [IdTipoUsuario]) VALUES (2, N'Manuel', N'Llanos', N'Matematica', N'llanos21@hotmail.com', N'918237744', 2)
INSERT [dbo].[Docente] ([Id], [Nombres], [Apellidos], [Especialidad], [Email], [Telefono], [IdTipoUsuario]) VALUES (3, N'd', N'd', N'df', N'we', N'789-789789', 2)
INSERT [dbo].[Docente] ([Id], [Nombres], [Apellidos], [Especialidad], [Email], [Telefono], [IdTipoUsuario]) VALUES (4, N'd', N'd', N'df', N'we', N'789-789789', 2)
INSERT [dbo].[Docente] ([Id], [Nombres], [Apellidos], [Especialidad], [Email], [Telefono], [IdTipoUsuario]) VALUES (5, N'd', N'd', N'df', N'we', N'789-789789', 2)
INSERT [dbo].[Docente] ([Id], [Nombres], [Apellidos], [Especialidad], [Email], [Telefono], [IdTipoUsuario]) VALUES (6, N'f', N'd342342s', N'ddd', N'dsd', N'112-312312', 1)
SET IDENTITY_INSERT [dbo].[Docente] OFF
INSERT [dbo].[Grado] ([Grado], [IdGrado]) VALUES (N'Primero', CAST(1 AS Numeric(5, 0)))
INSERT [dbo].[Grado] ([Grado], [IdGrado]) VALUES (N'Segundo', CAST(2 AS Numeric(5, 0)))
INSERT [dbo].[Grado] ([Grado], [IdGrado]) VALUES (N'Tercero', CAST(3 AS Numeric(5, 0)))
INSERT [dbo].[Grado] ([Grado], [IdGrado]) VALUES (N'Cuarto', CAST(4 AS Numeric(5, 0)))
INSERT [dbo].[Grado] ([Grado], [IdGrado]) VALUES (N'Quinto', CAST(5 AS Numeric(5, 0)))
SET IDENTITY_INSERT [dbo].[TipoUsuario] ON 

INSERT [dbo].[TipoUsuario] ([Id], [Descripcion], [Comentario]) VALUES (1, N'Docente', NULL)
INSERT [dbo].[TipoUsuario] ([Id], [Descripcion], [Comentario]) VALUES (2, N'Director', NULL)
INSERT [dbo].[TipoUsuario] ([Id], [Descripcion], [Comentario]) VALUES (3, N'Alumno', NULL)
SET IDENTITY_INSERT [dbo].[TipoUsuario] OFF
ALTER TABLE [dbo].[DetalleMatricula]  WITH CHECK ADD  CONSTRAINT [FK_DetalleMatricula_Cursos] FOREIGN KEY([IdCurso])
REFERENCES [dbo].[Cursos] ([id])
GO
ALTER TABLE [dbo].[DetalleMatricula] CHECK CONSTRAINT [FK_DetalleMatricula_Cursos]
GO
ALTER TABLE [dbo].[DetalleMatricula]  WITH CHECK ADD  CONSTRAINT [FK_DetalleMatricula_Matricula] FOREIGN KEY([IdMatricula])
REFERENCES [dbo].[Matricula] ([Id])
GO
ALTER TABLE [dbo].[DetalleMatricula] CHECK CONSTRAINT [FK_DetalleMatricula_Matricula]
GO
ALTER TABLE [dbo].[Docente]  WITH CHECK ADD  CONSTRAINT [FK_Docente_TipoUsuario] FOREIGN KEY([IdTipoUsuario])
REFERENCES [dbo].[TipoUsuario] ([Id])
GO
ALTER TABLE [dbo].[Docente] CHECK CONSTRAINT [FK_Docente_TipoUsuario]
GO
ALTER TABLE [dbo].[Grado]  WITH CHECK ADD  CONSTRAINT [FK_Grado_Cursos] FOREIGN KEY([IdGrado])
REFERENCES [dbo].[Cursos] ([id])
GO
ALTER TABLE [dbo].[Grado] CHECK CONSTRAINT [FK_Grado_Cursos]
GO
ALTER TABLE [dbo].[Matricula]  WITH CHECK ADD  CONSTRAINT [FK_Matricula_Alumno] FOREIGN KEY([IdAlumno])
REFERENCES [dbo].[Alumno] ([Id])
GO
ALTER TABLE [dbo].[Matricula] CHECK CONSTRAINT [FK_Matricula_Alumno]
GO
